select ro.id id, ro.house_details_id houseDetailsId,
        ro.external_id externalId, hd.phone phone, hd.part part,
        hd.tel_numbers telNumbers, hd.zone_square zoneSquare, hd.house_square houseSquare,
        hd.level level, hd.address address, hd.gas gas, hd.adv_info advInfo, hd.rooms rooms,
        p.state state, p.date date, p.price_value priceValue, hd.sewerage sewerage, hd.water water,
         (select if(p.price_value=sp.price_value,0,if(p.price_value>sp.price_value,1,-1))
                 from price sp
                 where
                     sp.realty_object_id = ro.id
                     and
                      sp.date < p.date
             order by sp.date desc limit 1
         ) dynamic

from realty_object ro
    join house_details hd on hd.id = ro.house_details_id
    join price p on p.realty_object_id = ro.id
     <%
    import org.apache.commons.lang.StringUtils;
    print(' where  date(p.date) = (select max(date(ssp.date)) from price ssp where ro.id = ssp.realty_object_id and');
    if(StringUtils.isNotEmpty(dateInterval1) && StringUtils.isNotEmpty(dateInterval2)){
        print(" date(ssp.date) >= date('${dateInterval1}') and date(ssp.date) <= date('${dateInterval2}') ");
    }else{
        if(StringUtils.isNotEmpty(dateInterval1))
            print(" date(ssp.date) >= date('${dateInterval1}') ")
        if(StringUtils.isNotEmpty(dateInterval2))
            print(" date(ssp.date) <= date('${dateInterval2}') ")
        if(StringUtils.isEmpty(dateInterval1) && StringUtils.isEmpty(dateInterval2)){
            print(" date(ssp.date) = (select max(date(date)) from price) ")
        }
    }
    print(") ");
 %>
order by p.state desc, ro.external_id asc